package com.fau.cm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.cm.entity.MedicalAdmin;
import com.fau.cm.entity.MedicalCallBlacklist;
import com.fau.cm.entity.MedicalCallCaller;
import com.fau.cm.entity.MedicalCallExhale;
import com.fau.cm.entity.MedicalCallTelInfo;
import com.fau.cm.service.ITelManageService;
import com.fau.cm.util.Page;
import com.fau.cm.util.ParamUtil;
import com.fau.cm.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class TelManageServiceImpl extends BaseService implements ITelManageService{
	
	@Override
	public Page findExhaleByPage(MedicalAdmin admin, MedicalCallExhale exhale, Map<String, Object> map) {
		List<Object> objectList = new ArrayList<Object>();
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalCallExhale m where 1=1 and m.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalCallExhale m where 1=1 and m.hospitalId=? ");
		objectList.add(admin.getEnterpriseId());
		if(exhale.getStartTime() != null){
			countSql.append("and m.outgoingTime >= ? ");
			statusSql.append("and m.outgoingTime >= ? ");
			objectList.add(exhale.getStartTime());
		}
		if(exhale.getEndTime() != null){
			countSql.append("and m.outgoingTime <= ? ");
			statusSql.append("and m.outgoingTime <= ? ");
			objectList.add(exhale.getEndTime());
		}
		if(StringUtils.isNotBlank(exhale.getOperator())){
			countSql.append("and m.operator = ? ");
			statusSql.append("and m.operator = ? ");
			objectList.add(exhale.getOperator());
		}
		if(StringUtils.isNotBlank(exhale.getExtensionNumber())){
			countSql.append("and m.extensionNumber = ? ");
			statusSql.append("and m.extensionNumber = ? ");
			objectList.add(exhale.getExtensionNumber());
		}
		if(StringUtils.isNotBlank(exhale.getOutgoingCalls())){
			countSql.append("and m.outgoingCalls like ? ");
			statusSql.append("and m.outgoingCalls like ? ");
			objectList.add("%"+exhale.getOutgoingCalls()+"%");
		}
		if(exhale.getBigCallDuration() != null){
			countSql.append("and m.callDuration >= ? ");
			statusSql.append("and m.callDuration >= ? ");
			objectList.add(exhale.getBigCallDuration());
		}
		if(exhale.getSmallCallDuration() != null){
			countSql.append("and m.callDuration <= ? ");
			statusSql.append("and m.callDuration <= ? ");
			objectList.add(exhale.getSmallCallDuration());
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.outgoingTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		@SuppressWarnings("unchecked")
		List<MedicalCallExhale> list = (List<MedicalCallExhale>) page.getResult();
		for (MedicalCallExhale medicalCallExhale : list) {
			MedicalAdmin medicalAdmin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, medicalCallExhale.getOperator());
			if(null != medicalAdmin) {
				medicalCallExhale.setOperatorName(medicalAdmin.getName());
			}
		}
		return page;
	}

	@Override
	public Page findCallerByPage(MedicalAdmin admin, MedicalCallCaller caller, Map<String, Object> map) {
		List<Object> objectList = new ArrayList<Object>();
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalCallCaller m where 1=1 and m.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalCallCaller m where 1=1 and m.hospitalId=? ");
		objectList.add(admin.getEnterpriseId());
		if(caller.getStartTime() != null){
			countSql.append("and m.callerTime >= ? ");
			statusSql.append("and m.callerTime >= ? ");
			objectList.add(caller.getStartTime());
		}
		if(caller.getEndTime() != null){
			countSql.append("and m.callerTime <= ? ");
			statusSql.append("and m.callerTime <= ? ");
			objectList.add(caller.getEndTime());
		}
		if(StringUtils.isNotBlank(caller.getAnswerSeat())){
			countSql.append("and m.answerSeat = ? ");
			statusSql.append("and m.answerSeat = ? ");
			objectList.add(caller.getAnswerSeat());
		}
		if(StringUtils.isNotBlank(caller.getWhetherAnswer())){
			countSql.append("and m.whetherAnswer = ? ");
			statusSql.append("and m.whetherAnswer = ? ");
			objectList.add(caller.getWhetherAnswer());
		}
		if(StringUtils.isNotBlank(caller.getCallerNum())){
			countSql.append("and m.callerNum like ? ");
			statusSql.append("and m.callerNum like ? ");
			objectList.add("%"+caller.getCallerNum()+"%");
		}
		if(StringUtils.isNotBlank(caller.getPhoneExtNum())){
			countSql.append("and m.phoneExtNum = ? ");
			statusSql.append("and m.phoneExtNum = ? ");
			objectList.add(caller.getPhoneExtNum());
		}
		if(StringUtils.isNotBlank(caller.getOutsideNum())){
			countSql.append("and m.outsideNum = ? ");
			statusSql.append("and m.outsideNum = ? ");
			objectList.add(caller.getOutsideNum());
		}
		if(StringUtils.isNotBlank(caller.getCity())){
			countSql.append("and m.city = ? ");
			statusSql.append("and m.city = ? ");
			objectList.add(caller.getCity());
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.callerTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalCallCaller> list = new ArrayList<MedicalCallCaller>();
		for(Object obj : page.getResult()) {
			MedicalCallCaller bean = (MedicalCallCaller) obj;
			if(StringUtils.isNotBlank(bean.getAnswerSeat())) {
				MedicalAdmin seat = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, bean.getAnswerSeat());
				if(null != seat){
					bean.setSeatName(seat.getName());
					list.add(bean);
				}
			}
		}
		page.setResult(list);
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportCaller(MedicalAdmin admin) throws Exception {
		// 获得记录
		List<MedicalCallCaller> list = this.iBaseDao.getBySql("from MedicalCallCaller m  where 1=1 and m.hospitalId=? ", null, admin.getEnterpriseId());
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_CALL_CALLER);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		// 遍历
		for (MedicalCallCaller caller : list) {
			i++;
			Row currentRow = sheet.createRow(i);
			currentRow.createCell(0).setCellValue(i);
			if(caller.getSerialNum() != null){
				currentRow.createCell(1).setCellValue(caller.getSerialNum());
			}
			if(caller.getOutsideNum() != null){
				currentRow.createCell(2).setCellValue(caller.getOutsideNum());
			}
			if(caller.getCallerNum() != null){
				currentRow.createCell(3).setCellValue(caller.getCallerNum());
			}
			if(caller.getCallerTime() != null){
				currentRow.createCell(4).setCellValue(caller.getCallerTime());
			}
			if(caller.getPhoneExtNum() != null){
				currentRow.createCell(5).setCellValue(caller.getPhoneExtNum());
			}
			if(caller.getWhetherAnswer() != null){
				if(SystemConfig.MEDICAL_ANSWERED_TYPE.equals(caller.getWhetherAnswer())){
					currentRow.createCell(6).setCellValue("已接起");
				}
				if(SystemConfig.MEDICAL_NO_ANSWER_TYPE.equals(caller.getWhetherAnswer())){
					currentRow.createCell(6).setCellValue("未接起");
				}
			}
			if(caller.getCallLength() != null){
				currentRow.createCell(7).setCellValue(caller.getCallLength());
			}
			if(caller.getAnswerSeat() != null){
				MedicalAdmin seat = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, caller.getAnswerSeat());
				if(null != seat){
					currentRow.createCell(8).setCellValue(seat.getName());
				}
			}
			if(caller.getOrdersCount() != null){
				currentRow.createCell(9).setCellValue(caller.getOrdersCount());
			}
			if(caller.getProvince() != null){
				currentRow.createCell(10).setCellValue(caller.getProvince());
			}
			if(caller.getCity() != null){
				currentRow.createCell(11).setCellValue(caller.getCity());
			}
		}
		return workbook;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportExhale(MedicalAdmin admin) throws Exception {
		// 获得记录
		List<MedicalCallExhale> list = this.iBaseDao.getBySql("from MedicalCallExhale m  where 1=1 and m.hospitalId=? ", null, admin.getEnterpriseId());
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_CALL_EXHALE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		// 遍历
		for (MedicalCallExhale cxhale : list) {
			i++;
			Row currentRow = sheet.createRow(i);
			currentRow.createCell(0).setCellValue(i);
			currentRow.createCell(1).setCellValue(cxhale.getExtensionNumber());
			currentRow.createCell(2).setCellValue(cxhale.getOutgoingCalls());
			currentRow.createCell(3).setCellValue(cxhale.getOutgoingTime());
			currentRow.createCell(4).setCellValue(cxhale.getHangUpTime());
			currentRow.createCell(5).setCellValue(cxhale.getCallDuration());
			currentRow.createCell(6).setCellValue(cxhale.getProvince());
			currentRow.createCell(7).setCellValue(cxhale.getCity());
			MedicalAdmin medicalAdmin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, cxhale.getOperator());
			currentRow.createCell(8).setCellValue(medicalAdmin.getName());
		}
		return workbook;
	}
	
	@Override
	public Workbook currentExportCaller(MedicalAdmin admin, String ids) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_CALL_CALLER);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(ids)){
			String [] ordersIds =  ids.split(",");
			for(int j=0;j<ordersIds.length;j++){
				// 获得记录
				MedicalCallCaller caller = (MedicalCallCaller)this.iBaseDao.getObject(MedicalCallCaller.class, ordersIds[j]);
				i++;
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				if(caller.getSerialNum() != null){
					currentRow.createCell(1).setCellValue(caller.getSerialNum());
				}
				if(caller.getOutsideNum() != null){
					currentRow.createCell(2).setCellValue(caller.getOutsideNum());
				}
				if(caller.getCallerNum() != null){
					currentRow.createCell(3).setCellValue(caller.getCallerNum());
				}
				if(caller.getCallerTime() != null){
					currentRow.createCell(4).setCellValue(caller.getCallerTime());
				}
				if(caller.getPhoneExtNum() != null){
					currentRow.createCell(5).setCellValue(caller.getPhoneExtNum());
				}
				if(caller.getWhetherAnswer() != null){
					if(SystemConfig.MEDICAL_ANSWERED_TYPE.equals(caller.getWhetherAnswer())){
						currentRow.createCell(6).setCellValue("已接起");
					}
					if(SystemConfig.MEDICAL_NO_ANSWER_TYPE.equals(caller.getWhetherAnswer())){
						currentRow.createCell(6).setCellValue("未接起");
					}
				}
				if(caller.getCallLength() != null){
					currentRow.createCell(7).setCellValue(caller.getCallLength());
				}
				
				if(caller.getAnswerSeat() != null){
					MedicalAdmin seat = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, caller.getAnswerSeat());
					currentRow.createCell(8).setCellValue(seat.getName());
				}
				if(caller.getOrdersCount() != null){
					currentRow.createCell(9).setCellValue(caller.getOrdersCount());
				}
				if(caller.getProvince() != null){
					currentRow.createCell(10).setCellValue(caller.getProvince());
				}
				if(caller.getCity() != null){
					currentRow.createCell(11).setCellValue(caller.getCity());
				}
			}
		}
		return workbook;
	}
	
	@Override
	public Workbook currentExportExhale(MedicalAdmin admin, String ids) throws Exception {
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_CALL_EXHALE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		int i=0;
		if(StringUtils.isNotBlank(ids)){
			String [] ordersIds =  ids.split(",");
			for(int j=0;j<ordersIds.length;j++){
				// 获得记录
				MedicalCallExhale exhale = (MedicalCallExhale)this.iBaseDao.getObject(MedicalCallExhale.class, ordersIds[j]);
				i++;
				Row currentRow = sheet.createRow(i);
				currentRow.createCell(0).setCellValue(i);
				if(exhale.getExtensionNumber() != null){
				currentRow.createCell(1).setCellValue(exhale.getExtensionNumber());
				}
				if(exhale.getOutgoingCalls() != null){
				currentRow.createCell(2).setCellValue(exhale.getOutgoingCalls());
				}
				if(exhale.getOutgoingTime() != null){
				currentRow.createCell(3).setCellValue(exhale.getOutgoingTime());
				}
				if(exhale.getHangUpTime() != null){
				currentRow.createCell(4).setCellValue(exhale.getHangUpTime());
				}
				if(exhale.getCallDuration() != null){
				currentRow.createCell(5).setCellValue(exhale.getCallDuration());
				}
				if(exhale.getProvince() != null){
				currentRow.createCell(6).setCellValue(exhale.getProvince());
				}
				if(exhale.getCity() != null){
				currentRow.createCell(7).setCellValue(exhale.getCity());
				}
				if(exhale.getOperator() != null){
					MedicalAdmin medicalAdmin = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, exhale.getOperator());
				currentRow.createCell(8).setCellValue(medicalAdmin.getName());
				}
			}
		}
		return workbook;
	}

	@Override
	public Page findBlacklistByPage(MedicalCallBlacklist blacklist, Map<String, Object> map) {
		StringBuilder statusSql = new StringBuilder("from MedicalCallBlacklist a where a.eid=? ");
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalCallBlacklist a where a.eid=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(admin.getEnterpriseId());
		
		if(StringUtils.isNotBlank(blacklist.getUname())){
			statusSql.append("and a.customer.name like ? ");
			countSql.append("and a.customer.name like ? ");
			objectList.add(blacklist.getUname()+"%");
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void insertBlacklist(MedicalCallBlacklist blacklist) {
		MedicalAdmin admin = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		blacklist.setEid(admin.getEnterpriseId());
		blacklist.setCustomer(null);
		this.iBaseDao.saveObject(blacklist);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void deleteBlacklist(MedicalCallBlacklist blacklist) {
		String ids[] = blacklist.getId().split(",");
		for(String id : ids) {
			this.iBaseDao.updateHql("delete from MedicalCallBlacklist m where m.id = ? ", id);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateBlacklist(MedicalCallBlacklist blacklist) {
		MedicalCallBlacklist callBlacklist = (MedicalCallBlacklist) this.iBaseDao.getObject(MedicalCallBlacklist.class, blacklist.getId());
		callBlacklist.setPhoneNum(blacklist.getPhoneNum());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalCallCaller> findCallRecords(String customerId, MedicalAdmin admin) {
		List<MedicalCallTelInfo> telInfoList = this.iBaseDao.getBySql("from MedicalCallTelInfo a where a.customerId=? ", null, customerId);
		List<MedicalCallCaller> list = new ArrayList<MedicalCallCaller>();
		for(MedicalCallTelInfo telInfo : telInfoList) {
			// 呼入
			List<MedicalCallCaller> callerList = this.iBaseDao.getBySql("from MedicalCallCaller a where a.callerNum=? and a.hospitalId=? ", null, telInfo.getPhoneNum(), admin.getEnterpriseId());
			for(MedicalCallCaller caller : callerList) {
				MedicalAdmin answerSeat = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, caller.getAnswerSeat());
				caller.setSeatName(answerSeat.getName());
				list.add(caller);
			}
			// 呼出
			List<MedicalCallExhale> exhaleList = this.iBaseDao.getBySql("from MedicalCallExhale a where a.outgoingCalls like ? and a.hospitalId=? ", null, "%"+telInfo.getPhoneNum(), admin.getEnterpriseId());
			for (MedicalCallExhale exhale : exhaleList) {
				MedicalCallCaller bean = new MedicalCallCaller();
				bean.setCallerNum(exhale.getExtensionNumber());		// 主叫
				bean.setOutsideNum(exhale.getOutgoingCalls());		// 被叫
				bean.setCallerTime(exhale.getOutgoingTime());		// 通话时间
				bean.setCallLength(exhale.getCallDuration());		// 通话时长
				MedicalAdmin answerSeat = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, exhale.getOperator());
				bean.setSeatName(answerSeat.getName());
				list.add(bean);
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page fingCallCallerByPage(MedicalCallCaller callCaller,Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(distinct m.callerNum) from MedicalCallCaller m where m.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("select distinct(m.callerNum) from MedicalCallCaller m where m.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(null != callCaller.getLeastCall()){
			countSql.append("and m.callerTime >= ? ");
			statusSql.append("and m.callerTime >= ? ");
			objectList.add(callCaller.getLeastCall());
		}
		if(null != callCaller.getFirstCall()){
			countSql.append("and m.callerTime <= ? ");
			statusSql.append("and m.callerTime <= ? ");
			objectList.add(callCaller.getFirstCall());
		}
		if(StringUtils.isNotBlank(callCaller.getCallerNum())){
			countSql.append("and m.callerNum = ? ");
			statusSql.append("and m.callerNum = ? ");
			objectList.add(callCaller.getCallerNum());
		}
		if(null != callCaller.getOrdersCount()){
			countSql.append("and m.ordersCount = ? ");
			statusSql.append("and m.ordersCount = ? ");
			objectList.add(callCaller.getOrdersCount());
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by m." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by m.callerTime desc ");
		}
		
		Page p = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<String> list = (List<String>) p.getResult();
		List<MedicalCallCaller> callList = new LinkedList<MedicalCallCaller>();
		for (String s : list) {
			MedicalCallCaller temp = new MedicalCallCaller();
			temp.setCallerNum(s);
			List<MedicalCallCaller> list1 = this.iBaseDao.getBySql("from MedicalCallCaller a where a.callerNum=? order by a.callerTime desc ", 1, s);
			temp.setLeastCall(list1.get(0).getCallerTime());
			List<MedicalCallCaller> list2 = this.iBaseDao.getBySql("from MedicalCallCaller a where a.callerNum=? order by a.callerTime asc ", 1, s);
			temp.setFirstCall(list2.get(0).getCallerTime());
			int count1 = this.iBaseDao.getIntCount("select count(a.id) from MedicalCallCaller a where a.callerNum=? and a.whetherAnswer=? ", s,SystemConfig.MEDICAL_ANSWERED_TYPE);
			temp.setAnswerCount(count1);
			int count2 = this.iBaseDao.getIntCount("select count(a.id) from MedicalCallCaller a where a.callerNum=? ", s);
			temp.setCallCount(count2);
			Long count3 = new Long(this.iBaseDao.getIntCount("select count(a.ordersCount) from MedicalCallCaller a where a.callerNum=? ", s));
			String toInt = count3.toString();
			temp.setOrdersCount(Integer.valueOf(toInt));
			String province = (String) this.iBaseDao.getObjectByHql("select a.province from MedicalCallCaller a where a.callerNum=? ", s);
			temp.setProvince(province);
			callList.add(temp);
		}
		p.setResult(callList);
		return p;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook currentDuplicateExport(MedicalAdmin admin, MedicalCallCaller callCaller)throws Exception {
		StringBuilder statusSql = new StringBuilder("select distinct(m.callerNum) from MedicalCallCaller m where m.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(null != callCaller.getLeastCall()){
			statusSql.append("and m.callerTime >= ? ");
			objectList.add(callCaller.getLeastCall());
		}
		if(null != callCaller.getFirstCall()){
			statusSql.append("and m.callerTime <= ? ");
			objectList.add(callCaller.getFirstCall());
		}
		if(StringUtils.isNotBlank(callCaller.getCallerNum())){
			statusSql.append("and m.callerNum = ? ");
			objectList.add(callCaller.getCallerNum());
		}
		if(null != callCaller.getOrdersCount()){
			statusSql.append("and m.ordersCount = ? ");
			objectList.add(callCaller.getOrdersCount());
		}
		
		List<String> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_DUPLICATEL_EXHALE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
				
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		int size = list.size();
		for(int i = 0;i<size;i++){
			Row currentRow = sheet.createRow(i+1);
			String tempNum = list.get(i);
			currentRow.createCell(0).setCellValue(tempNum);
			List<MedicalCallCaller> list1 = this.iBaseDao.getBySql("from MedicalCallCaller a where a.callerNum=? order by a.callerTime desc ", 1, tempNum);
			currentRow.createCell(1).setCellValue(DateFormatUtils.format(list1.get(0).getCallerTime(), "yyyy-MM-dd HH:mm:ss"));
			List<MedicalCallCaller> list2 = this.iBaseDao.getBySql("from MedicalCallCaller a where a.callerNum=? order by a.callerTime asc ", 1, tempNum);
			currentRow.createCell(3).setCellValue(DateFormatUtils.format(list2.get(0).getCallerTime(), "yyyy-MM-dd HH:mm:ss"));
			int count1 = this.iBaseDao.getIntCount("select count(a.id) from MedicalCallCaller a where a.callerNum=? and a.whetherAnswer=? ", tempNum,SystemConfig.MEDICAL_ANSWERED_TYPE);
			currentRow.createCell(2).setCellValue(count1);
			int count2 = this.iBaseDao.getIntCount("select count(a.id) from MedicalCallCaller a where a.callerNum=? ", tempNum);
			currentRow.createCell(4).setCellValue(count2);
			Long count3 = (Long) this.iBaseDao.getObjectByHql("select sum(a.ordersCount) from MedicalCallCaller a where a.callerNum=? ", tempNum);
			currentRow.createCell(6).setCellValue(count3.toString());
			String province = (String) this.iBaseDao.getObjectByHql("select a.province from MedicalCallCaller a where a.callerNum=? ", tempNum);
			currentRow.createCell(5).setCellValue(province);
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Page fingDuplicateCallCallerByPage(MedicalCallCaller callCaller,Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(distinct m.outsideNum) from MedicalCallCaller m where m.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("select distinct(m.outsideNum) from MedicalCallCaller m where m.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(null != callCaller.getLeastCall()){
			countSql.append("and m.callerTime >= ? ");
			statusSql.append("and m.callerTime >= ? ");
			objectList.add(callCaller.getLeastCall());
		}
		if(null != callCaller.getFirstCall()){
			countSql.append("and m.callerTime <= ? ");
			statusSql.append("and m.callerTime <= ? ");
			objectList.add(callCaller.getFirstCall());
		}
		
		Page p = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<String> list = (List<String>) p.getResult();
		List<MedicalCallCaller> callList = new LinkedList<MedicalCallCaller>();
		for (String s : list) {
			MedicalCallCaller temp = new MedicalCallCaller();
			temp.setOutsideNum(s);
			int totle = this.iBaseDao.getIntCount("select count(*) from MedicalCallCaller a where a.outsideNum=? ", s);
			temp.setCallCount(totle);
			List<MedicalCallCaller> l = this.iBaseDao.getBySql("from MedicalCallCaller a where a.outsideNum=? group by a.callerNum ",null, s);
			int noRepeatCallCount = l.size();
			temp.setNoRepeatCallCount(noRepeatCallCount+"");
			temp.setRepeatCallCount((totle-noRepeatCallCount)+"");
			callList.add(temp);
		}
		p.setResult(callList);
		return p;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook duplicateStaExport(MedicalAdmin admin, MedicalCallCaller callCaller)throws Exception {
		StringBuilder statusSql = new StringBuilder("select distinct(m.outsideNum) from MedicalCallCaller m where m.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(null != callCaller.getLeastCall()){
			statusSql.append("and m.callerTime >= ? ");
			objectList.add(callCaller.getLeastCall());
		}
		if(null != callCaller.getFirstCall()){
			statusSql.append("and m.callerTime <= ? ");
			objectList.add(callCaller.getFirstCall());
		}
		
		List<String> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		// Excel模版对象
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.EXPORT_DUPLICATEL_STA_EXHALE);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
				
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		int size = list.size();
		for(int i = 0;i<size;i++){
			Row currentRow = sheet.createRow(i+1);
			String tempNum = list.get(i);
			currentRow.createCell(0).setCellValue(tempNum);
			int totle = this.iBaseDao.getIntCount("select count(*) from MedicalCallCaller a where a.outsideNum=? ", tempNum);
			currentRow.createCell(1).setCellValue(totle);
			List<MedicalCallCaller> l = this.iBaseDao.getBySql("from MedicalCallCaller a where a.outsideNum=? group by a.callerNum ",null, tempNum);
			int noRepeatCallCount = l.size();
			currentRow.createCell(3).setCellValue(noRepeatCallCount+"");
			currentRow.createCell(2).setCellValue((totle-noRepeatCallCount)+"");
		}
		
		return workbook;
	}
	
}
